package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.CartDao;
import com.foreknow.elm.po.Business;
import com.foreknow.elm.po.Cart;
import com.foreknow.elm.po.Food;
import com.foreknow.elm.po.User;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author shkstart
 * @create 2021-08-26 16:54
 */
public class CartDaoImpl implements CartDao {
    private Connection con = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    private int row;

    /**
     * 根据用户编号查询此用户所有购物车信息 根据用户编号和商家编号，查询此用户购物车中某个商家的所有购物车信息
     * @param userId
     * @param businessId
     * @return
     */
    @Override
    public List<Object> listCart(String userId, int businessId) throws SQLException {


//        String sql = "SELECT * from cart  c,user u,business b  where c.businessId=b.businessId AND u.userId=c.userId AND c.userId =? and c.businessId=?  ";
////        String sql="SELECT * from cart where userId =?";
//        try {
//            //获取连接
//            con = DBUtil.getConnection();
//            pst = con.prepareStatement(sql);
//            pst.setString(1, userId);
//            pst.setInt(2, businessId);
//
//            rs = pst.executeQuery();
//
//            while (rs.next()) {
//                Cart cart = new Cart();
//                cart.setCartId(rs.getInt("cartId"));
//                cart.setFoodId(rs.getInt("foodId"));
//                cart.setBusinessId(rs.getInt("businessId"));
//                cart.setUserId(rs.getString("userId"));
//                cart.setQuantity(rs.getInt("quantity"));
//                carts.add(cart);
//
//                User user = new User();
//                user.setUserId(rs.getString("userId"));
//                user.setPassword(rs.getString("password"));
//                user.setUserName(rs.getString("userName"));
//                user.setUserSex(rs.getInt("userSex"));
//                user.setUserImg(rs.getString("userImg"));
//                user.setDelTag(rs.getInt("delTag"));
//                carts.add(user);
//
//                Business business = new Business();
//                business.setBusinessId(rs.getInt("businessId"));
//                business.setBusinessName(rs.getString("businessName"));
//                business.setBusinessAddress(rs.getString("businessAddress"));
//                business.setBusinessExplain(rs.getString("businessExplain"));
//                business.setBusinessImg(rs.getString("businessImg"));
//                business.setOrderTypeId(rs.getInt("orderTypeId"));
//                business.setStarPrice(rs.getDouble("starPrice"));
//                business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
//                business.setRemarks(rs.getString("remarks"));
//                carts.add(business);
//
//
//            }
//        } finally {
//            DBUtil.close(rs, pst);
//        }
//
//        return carts;

        List<Object> carts = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        sql.append(" select c.*, ");
        sql.append(" f.foodId ffoodId, ");
        sql.append(" f.foodName ffoodName, ");
        sql.append(" f.foodExplain ffoodExplain, ");
        sql.append(" f.foodImg ffoodImg, ");
        sql.append(" f.foodPrice ffoodPrice, ");
        sql.append(" f.businessId fbusinessId, ");
        sql.append(" f.remarks fremarks, ");
        sql.append(" b.businessId bbusinessId, ");
        sql.append(" b.businessName bbusinessName, ");
        sql.append(" b.businessAddress bbusinessAddress, ");
        sql.append(" b.businessExplain bbusinessExplain, ");
        sql.append(" b.businessImg bbusinessImg, ");
        sql.append(" b.orderTypeId borderTypeId, ");
        sql.append(" b.starPrice bstarPrice, ");
        sql.append(" b.deliveryPrice bdeliveryPrice");
        sql.append(" from (cart c left join food f on c.foodId=f.foodId) ");
        sql.append("      left join business b on c.businessId=b.businessId ");
        sql.append(" where 1=1 ");
        if (businessId != 0) {
            sql.append(" and c.businessId=" + businessId);
        }
        if (userId != null) {
            sql.append(" and c.userId='" + userId + "'");
        }
        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                Cart c = new Cart();
                c.setCartId(rs.getInt("cartId"));
                c.setFoodId(rs.getInt("foodId"));
                c.setBusinessId(rs.getInt("businessId"));
                c.setUserId(rs.getString("userId"));
                c.setQuantity(rs.getInt("quantity"));
                Food f = new Food();
                f.setFoodId(rs.getInt("ffoodId"));
                f.setFoodName(rs.getString("ffoodName"));
                f.setFoodExplain(rs.getString("ffoodExplain"));
                f.setFoodImg(rs.getString("ffoodImg"));
                f.setFoodPrice(rs.getDouble("ffoodPrice"));
                f.setBusinessId(rs.getInt("fbusinessId"));
                f.setRemarks(rs.getString("fremarks"));
                c.setFood(f);
                Business b = new Business();
                b.setBusinessId(rs.getInt("bbusinessId"));
                b.setBusinessName(rs.getString("bbusinessName"));
                b.setBusinessAddress(rs.getString("bbusinessAddress"));
                b.setBusinessExplain(rs.getString("bbusinessExplain"));
                b.setBusinessImg(rs.getString("bbusinessImg"));
                b.setOrderTypeId(rs.getInt("borderTypeId"));
                b.setStarPrice(rs.getDouble("bstarPrice"));
                b.setDeliveryPrice(rs.getDouble("bdeliveryPrice"));
                c.setBusiness(b);
                carts.add(c);
            }
        } finally {
            DBUtil.close(rs, pst);
        }
        return carts;
    }


    /**
     * CartController/saveCart 参数：userId、businessId、foodId 返回值：int（影响的行数）
     * 功能：向购物车表中添加一条记录
     */
    @Override
    public int saveCart(String userId, int businessId, int foodId) throws SQLException {

        String sql = "INSERT INTO cart (userId,businessId,foodId) VALUES(?,?,?)";
        try {
            //获取连接

            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, userId);
            pst.setInt(2, businessId);
            pst.setInt(3, foodId);
            row = pst.executeUpdate();
            return row;

        } finally {
            DBUtil.close(rs, pst);
        }
    }
    /**
     * CartController/updateCart 参数：userId、businessId、foodId、quantity 返回值：int（影响的行数）
     *
     */
    /**
     * 功能：根据用户编号、商家编号、食品编号更新数量
     *
     * @param userId
     * @param businessId
     * @param foodId
     * @param quantity
     * @return
     */
    @Override
    public int updateCart(String userId, int businessId, int foodId, int quantity) {

        String sql = "UPDATE cart SET quantity=? WHERE userId= ? AND businessId=? AND foodId=?";
        try {

            //获取连接
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, quantity);
            pst.setString(2, userId);
            pst.setInt(3, businessId);
            pst.setInt(4, foodId);

            row = pst.executeUpdate();
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pst);
        }
        return 0;
    }

    /**
     * 功能：根据用户编号、商家编号、食品编号删除购物车表中的一条食品记录 根据用户编号、商家编号删除购物车表中的多条条记录
     *
     * @return
     */
    @Override
    public int removeCart(Cart cart) {
//        String sql="DELETE FROM cart WHERE userId = ? AND businessId=? AND foodId=?";
//        try{
//            //获取连接
//            con=DBUtil.getConnection();
//            pst=con.prepareStatement(sql);
//            pst.setString(1,userId);
//            pst.setInt(2,businessId);
//            pst.setInt(3,foodId);
//            row=pst.executeUpdate();
//            return row;
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return 0;
        String  userId=cart.getUserId();
        int businessId=cart.getBusinessId();
        int foodId=cart.getFoodId();
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM cart");
        sql.append("  where 1=1");
        if (userId != null) {
            sql.append("  and userId=" + userId);
        }
        if (businessId != 0) {
            sql.append("  and businessId=" + businessId);

        }
        if (foodId != 0) {
            sql.append("  and foodId=" + foodId);
        }

        try {
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql.toString());
            int i = pst.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
